﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spire.Xls;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using CronCommon;
using System.Collections;

namespace ExportFile
{

    public class FortmatColumnType
    {
        public int id;
        public string 栏位名称;
        public string FormatMask="";
        public string 计算方式="";

        public FortmatColumnType(string formattype)
        {
            id = -1;
            var s = formattype.Split(':').ToArray();
            int c = s.Count();
            if (c > 0) 栏位名称 = s[0];
            if (c > 1) FormatMask = s[1];
            if (c > 2) 计算方式 = s[2].ToUpper();
        }
    }
    public static class ExcelConvertPdf
    {
        public static bool XLSConvertToPDF(string sourcePath, string targetPath)
        {
            bool result = false;

            try
            {
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(sourcePath, ExcelVersion.Version97to2003);

                //保存Excel
                workbook.SaveToFile(targetPath, Spire.Xls.FileFormat.PDF);

                workbook = null;

                result = true;
            }
            catch (Exception e)
            {
                result = false;
                throw new Exception("转换PDF失败" + e.Message);
            }
            return result;
        }
    }


    public class ExportFile
    {
        private Workbook workbook;
        private Worksheet sheet;
        private int startSheet = 0;
        private string filename_suffix = null;
        private bool isFormatType = false;
        private bool is计算方式 = false;

        private List<FortmatColumnType> formatlist = new List<FortmatColumnType>();


        //文件路径
        private string Path { get; set; }
        //保存文件名称
        private string Savefile { get; set; }

        private string ExternName { get; set; }


        public int Startrow { get; set; }

        public ExportFile()
        {
            //创建一个工作簿
            workbook = new Workbook();

        }

        public ExportFile(string path) : this()
        {
            this.Path = path;
        }

        public ExportFile(string path, string savefile, string extername) : this(path)
        {
            this.Savefile = savefile.ToLower();
            this.ExternName = extername.ToLower();
        }

        public ExportFile(string path, string savefile, string extername, string filename_suffix) : this(path, savefile, extername)
        {
            //为文件名做加工,增加后缀
            this.filename_suffix = filename_suffix;
        }

        public ExportFile(string path, string savefile, string extername, string filename_suffix, string fortmatcolumntype) : this(path, savefile, extername, filename_suffix)
        {
            FormatColumnType(fortmatcolumntype);
        }

        /// <summary>
        /// 初始化格式字串
        /// </summary>
        /// <param name="FormatString"></param>
        public void FormatColumnType(string FormatString)
        {
            formatlist.Clear();
            foreach (var s in FormatString.Split(';').ToList())
            {
                formatlist.Add(new FortmatColumnType(s));
            }
            if (formatlist.FindIndex(x => x.FormatMask != "") != -1) this.isFormatType = true;
            if (formatlist.FindIndex(x => x.计算方式 != "") != -1) this.is计算方式 = true;
            Log.Writter($"加载过滤字串:{FormatString}");
            Log.Writter($"判断启动格式化功能:{isFormatType.ToString()}");
            Log.Writter($"判断启动计算栏位:{is计算方式.ToString()}");            
        }

        public string Xls_int2col(int acol)
        {
            string Result = string.Empty;
            if (acol <= 1)
            {
                Result = "A";
            }
            else
            {
                if (acol >= 256)
                {
                    Result = "IV";
                }
                else
                {
                    if (acol > 26)
                    {
                        if (acol % 26 == 0)
                        {
                            byte[] array = new byte[2];
                            array[0] = (byte)(64 + (int)(acol / 26) - 1);
                            array[1] = (byte)(64 + 26);
                            Result = Convert.ToString(System.Text.Encoding.ASCII.GetString(array));
                        }
                        else
                        {
                            byte[] array = new byte[2];
                            array[0] = (byte)(64 + (int)(acol / 26));
                            array[1] = (byte)(64 + (int)(acol % 26));
                            Result = Convert.ToString(System.Text.Encoding.ASCII.GetString(array));
                        }
                    }
                    else
                    {
                        byte[] array = new byte[1];
                        array[0] = (byte)(64 + acol);
                        Result = Convert.ToString(System.Text.Encoding.ASCII.GetString(array));
                    }
                }
            }
            return Result;
        }

        public string RangArray(int srow, int scol, int drow, int dcol)
        {
            return $"{Xls_int2col(scol)}{srow.ToString()}:{Xls_int2col(dcol)}{drow.ToString()}";
        }

        public string SaveFile()
        {
            if (string.IsNullOrWhiteSpace(Path))
            {
                Log.Writter("需要保存文件路径为空");
                return null;
            }

            if (string.IsNullOrWhiteSpace(Savefile))
            {
                this.Savefile = DateTime.Now.ToString("yyyyMMdd_HHmmss");

            }

            if (string.IsNullOrWhiteSpace(ExternName))
            {
                this.ExternName = "xls";
            }

            string saveFilename = $"{Path}\\{Savefile}";

            if (!string.IsNullOrWhiteSpace(filename_suffix))
            {
                saveFilename = $"{saveFilename}{DateTime.Now.ToString(filename_suffix)}";
            }

            if (this.ExternName == "xls")
            {
                if (File.Exists(saveFilename + ".xls"))
                {
                    File.Delete(saveFilename + ".xls");
                    Log.Writter("文件已存在,删除文件" + saveFilename + ".xls");
                }
                workbook.SaveToFile((saveFilename + ".xls"), ExcelVersion.Version97to2003);
            }
            else if (this.ExternName == "xlsx")
            {
                if (File.Exists(saveFilename + ".xlsx"))
                {
                    File.Delete(saveFilename + ".xlsx");
                    Log.Writter("文件已存在,删除文件" + saveFilename + ".xlsx");
                }
                workbook.SaveToFile((saveFilename + ".xlsx"), ExcelVersion.Version2010);
            }
            else if (this.ExternName == "pdf")
            {
                if (File.Exists(saveFilename + ".pdf"))
                {
                    File.Delete(saveFilename + ".pdf");
                    Log.Writter("文件已存在,删除文件" + saveFilename + ".pdf");
                }
                workbook.SaveToFile((saveFilename + ".pdf"), Spire.Xls.FileFormat.PDF);

            }


            sheet = null;
            workbook = null;

            Log.Writter($"文件生成完成 {saveFilename}.{this.ExternName}");
            return $"{ saveFilename}.{this.ExternName}";

        }

        public void Exportexcel(DataTable dt, bool hasTitle)
        {
            try
            {
                if (startSheet > workbook.Worksheets.Count - 1)
                    workbook.CreateEmptySheet();

                sheet = workbook.Worksheets[startSheet];

                sheet.Name = dt.TableName;

                //重新初始化过滤字串列表
                if (formatlist.Count > 0)
                {
                    foreach (var s in formatlist)
                    {
                        s.id = -1;
                        this.isFormatType = false;
                        this.is计算方式 = false;
                    }
                }

                if (hasTitle) //表头
                {
                    for (var j = 0; j < dt.Columns.Count; j++)
                    {
                        if ((dt.Columns[j].ColumnName.Trim().ToUpper().Equals("MAILTO")) || (dt.Columns[j].ColumnName.Trim().ToUpper().Equals("MAILCC")))
                            continue;
                        sheet.Range[1, j + 1].Text = dt.Columns[j].ColumnName.Trim();

                        if (formatlist.Count > 0)
                        {
                            if (formatlist.FindIndex(x => x.栏位名称 == dt.Columns[j].ColumnName.Trim()) != -1)
                            {
                                formatlist[formatlist.FindIndex(x => x.栏位名称 == dt.Columns[j].ColumnName.Trim())].id = j;
                                if (formatlist.FindIndex(x =>x.id> -1 && x.FormatMask != "") != -1) this.isFormatType = true;
                                if (formatlist.FindIndex(x =>x.id>-1 &&x.计算方式 != "") != -1) this.is计算方式 = true;
                            }
                        }
                        sheet.Range[1, j + 1].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;//边框
                        sheet.Range[1, j + 1].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;//边框
                        sheet.Range[1, j + 1].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;//边框
                        sheet.Range[1, j + 1].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;//边框
                    }
                }

                //循环表数据
                for (var i = 0; i < dt.Rows.Count; i++)//循环赋值
                {
                    for (var j = 0; j < dt.Columns.Count; j++)
                    {
                        if ((dt.Columns[j].ColumnName.Trim().ToUpper().Equals("MAILTO")) || (dt.Columns[j].ColumnName.Trim().ToUpper().Equals("MAILCC")))
                            continue;
                        var dyg = sheet.Range[i + 2, j + 1];
                        dyg.Value = dt.Rows[i][j].ToString();

                        if (isFormatType)
                        {
                            if (formatlist.FindIndex(x => x.id == j) != -1)
                            {
                                if (formatlist[formatlist.FindIndex(x => x.id == j)].FormatMask != "")
                                    dyg.NumberFormat = formatlist[formatlist.FindIndex(x => x.id == j)].FormatMask;
                            }
                        }

                        dyg.Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;//边框
                        dyg.Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
                        dyg.Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
                        dyg.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;

                        //最后一行的处理方式
                        if (i == dt.Rows.Count - 1)
                        {
                            if (is计算方式)
                            {
                                if (formatlist.FindIndex(x => x.id == j) != -1)
                                {
                                    if (formatlist[formatlist.FindIndex(x => x.id == j)].计算方式 != "")
                                    {
                                        string[] vs = { "SUM", "COUNT", "AVERAGE", "MAX", "MIN" };
                                        string 计算方式 = formatlist[formatlist.FindIndex(x => x.id == j)].计算方式;

                                        if (vs.Contains(计算方式))
                                        {
                                            sheet.Range[i + 3, j + 1].Formula = $"={计算方式}({RangArray(2, j + 1, i + 2, j + 1)})";
                                        }
                                        else
                                        {
                                            sheet.Range[i + 3, j + 1].Value = 计算方式;
                                        }
                                    }

                                    if (formatlist[formatlist.FindIndex(x => x.id == j)].FormatMask != "")
                                        sheet.Range[i + 3, j + 1].NumberFormat = formatlist[formatlist.FindIndex(x => x.id == j)].FormatMask;
                                }

                                sheet.Range[i + 3, j + 1].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;//边框
                                sheet.Range[i + 3, j + 1].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
                                sheet.Range[i + 3, j + 1].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
                                sheet.Range[i + 3, j + 1].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
                            }
                        }
                    }
                }

                //自动栏宽设定
                for (int i = 1; i < sheet.Columns.Length; i++)
                {
                    sheet.AutoFitColumn(i);
                }

                //有计算栏位的自动栏宽
                if (is计算方式)
                {
                    for (var j = 0; j < dt.Columns.Count; j++)
                    {
                        if (formatlist.FindIndex(x => x.id == j) != -1)
                        {
                            if (formatlist[formatlist.FindIndex(x => x.id == j)].计算方式 != "")
                            {
                                sheet.Range[1, j+1].ColumnWidth = sheet.Range[1, j+1].ColumnWidth + 3;
                               // sheet.AllocatedRange[dt.Rows.Count+2, j + 1].AutoFitColumns();
                            }
                        }
                    }
                }

                sheet = null;

                startSheet++;
            }
            catch (Exception ex)
            {
                throw new Exception("转Excel出错" + ex.Message);
            }
        }

        public int GetLength(string str)
        {
            if (str.Length == 0)
                return 0;
            ASCIIEncoding ascii = new ASCIIEncoding();
            int tempLen = 0;
            byte[] s = ascii.GetBytes(str);
            for (int i = 0; i < s.Length; i++)
            {
                if ((int)s[i] == 63)
                {
                    tempLen += 2;
                }
                else
                {
                    tempLen += 1;
                }
            }
            return tempLen;
        }

    }
}